package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.PbeEntity;
import com.css.fxfzypg.modules.baseData.queryParams.PbeQueryParams;
import com.css.fxfzypg.modules.baseData.repository.PbeRepository;
import com.css.fxfzypg.modules.baseData.vo.PbeEntityVO;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * @author y1fan
 * @create 2022-08-11 9:56
 */
@Repository
public class PbeRepositoryImpl implements PbeRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public PbeEntity findById(String id) {
        String sql="";
        sql="select * from data_pbe where id='"+id+"'";
        PbeEntity pbeEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(PbeEntity.class));
        return pbeEntity;
    }

    @Override
    public PbeEntity savePbe(PbeEntity pbeEntity) {
        String sql = "";
        sql = "insert into data_pbe values(:id, :population, :coveredarea, :gdp, :townorcontry, :isdelete, :creattime) ";


        SqlParameterSource source = new BeanPropertySqlParameterSource(pbeEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return pbeEntity;
    }

    @Override
    public PbeEntity updatePbe(PbeEntity pbeEntity) {
        String sql="";
        sql="update data_pbe  set  population = '"+pbeEntity.getPopulation()+"', coveredArea = '"+pbeEntity.getCoveredarea()+"', gdp = '"+pbeEntity.getGdp()+"', townOrContry = '"+pbeEntity.getTownorcontry()+"', isdelete = '"+pbeEntity.getIsdelete()+"', creatTime ='"+pbeEntity.getCreattime()+"' where id='"+pbeEntity.getId()+"'";
        jdbcTemplate.update(sql);
        return pbeEntity;
    }

    @Override
    public void deletePbe(List<String> ids) {
        String sql="";
        sql="update data_pbe set isdelete ='"+YNEnum.N.toString()+"' where id in ('"+ids+"')";
        jdbcTemplate.update(sql);

    }

    @Override
    public Page<PbeEntity> getAllPbeByParams(PbeQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select u.* from data_pbe u  where isdelete='"+YNEnum.Y.toString()+"'");

        String wholeSql = sql.toString();
        List<PbeEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(PbeEntity.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());
        Page<PbeEntity> pbeEntities = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return pbeEntities;

    }

    @Override
    public List<PbeEntity> exportExcel() {
        return jdbcTemplate.query("select u.* from data_pbe u where u.isdelete='"+YNEnum.Y.toString()+"' ", new BeanPropertyRowMapper<>(PbeEntity.class));

    }

    @Override
    public void importExcel(List<PbeEntity> pbeEntities) {
        jdbcTemplate.batchUpdate("insert into data_pbe (id,population,coveredarea,gdp,townorcontry,isdelete,creattime)values(?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1,pbeEntities.get(i).getId());
                ps.setFloat(2,pbeEntities.get(i).getPopulation());
                ps.setFloat(3,pbeEntities.get(i).getCoveredarea());
                ps.setFloat(4,pbeEntities.get(i).getGdp());
                ps.setString(5,pbeEntities.get(i).getTownorcontry());
                ps.setString(6,pbeEntities.get(i).getIsdelete());
                java.sql.Date sqlDate = new java.sql.Date(pbeEntities.get(i).getCreattime().getTime());
                ps.setDate(7,  sqlDate);
            }

            @Override
            public int getBatchSize() {
                return pbeEntities.size();
            }
        });
    }

    @Override
    public PbeEntity fingPbeByPbeId(String pbeId) {
        String sql = "select * from data_pbe where isdelete='"+YNEnum.N.toString()+"' and pbe_id='"+pbeId+"'";
        List<PbeEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntity.class));
        return list.size()>0?list.get(0):null;
    }

    @Override
    public List<PbeEntity> getPbeByInfluenceflieId(String id) {
        String sql =
                "SELECT *,ST_AsText(geom) geomText FROM data_pbe t WHERE  ST_Intersects(t.geom,(select a.geom FROM setting_seismic_influenceflied a WHERE a.uuid = '"+id+"')) and isdelete='"+YNEnum.N.toString() +"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntity.class));
    }

    @Override
    public List<PbeEntityVO> getPbe2ByInfluenceflieId(String id) {
        String sql =
                "SELECT population,gdp FROM data_pbe t " +
                        "WHERE  ST_Intersects(t.geom,(select a.geom FROM seismic_influencefly_pbe a WHERE a.influencefly_id = ?)) and isdelete='"+YNEnum.N.toString() +"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntityVO.class),id);
    }

    @Override
    public List<PbeEntity> getPbeInfo(String prefecture, String code,String taskId) {
        //String sql="SELECT *,ST_AsText(geom) geomText FROM data_pbe t WHERE ST_Intersects(t.geom,(select a.geom FROM district_boundary_county a WHERE a.name = '"+prefecture+"' and a.code= '"+code+"')) and isdelete='"+YNEnum.N.toString() +"'";
            String sql= "SELECT *,ST_AsText(st_intersection((select geom FROM ypg_preassess_task WHERE id = ? ),s.geom)) geomText " +
                    "FROM (SELECT * FROM data_pbe t WHERE ST_Intersects(t.geom,(select a.geom FROM district_boundary_county a " +
                    "WHERE a.name = ? and a.code= ?)) and isdelete='"+YNEnum.N.toString() +"') s";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntity.class),taskId,prefecture,code);
    }
    @Override
    public List<PbeEntity> getPbeInfo1(String taskId) {
        //String sql="SELECT *,ST_AsText(geom) geomText FROM data_pbe t WHERE ST_Intersects(t.geom,(select a.geom FROM district_boundary_county a WHERE a.name = '"+prefecture+"' and a.code= '"+code+"')) and isdelete='"+YNEnum.N.toString() +"'";
        String sql= "SELECT *,ST_AsText(s.geom) geomText FROM (SELECT * FROM data_pbe t WHERE ST_Intersects(" +
                "(select geom FROM ypg_preassess_task WHERE id = ? ),t.geom) " +
                "and isdelete='"+YNEnum.N.toString() +"' order by t.jglx_type) s ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntity.class),taskId);
    }

    @Override
    public Integer countByCounty(String name, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_pbe where county = '" + county + "' and city = '" + city + "' and province = '" + name + "'  or (province ='"+name+"' and city ='' and county ='')  and isdelete = '0' ", Integer.class);
    }

    @Override
    public Integer countByCity(String name, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_pbe where city = '" + city + "' and province = '" + name + "'   or (province ='"+name+"' and city ='' and county ='')   and isdelete = '0'", Integer.class);
    }

    @Override
    public Integer countByProvince(String name) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_pbe where province = '" + name + "' and isdelete = '0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(t1.*) from data_pbe t1 LEFT JOIN qh_map_files t2 on t1.fileuuid = t2.uuid where t1.province = ? and (t2.update_time LIKE ? or t2.create_time LIKE ?) and t1.isdelete = '0' and t2.isdelete = '0'", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");

    }

    //todo 查不到会报错，不影响业务，可以优化
    @Override
    public List<PbeEntityVO> getPbe2ByInfluenceflieIdAndTaskId(String influencefliedId, String taskId) {
        String sql="SELECT population,gdp  FROM data_pbe T WHERE ST_Intersects ( T.geom, ( SELECT A.geom FROM seismic_influencefly_pbe A WHERE A.influencefly_id = '"+influencefliedId+"' ) ) AND ST_Intersects ( T.geom, ( SELECT B.geom FROM ypg_preassess_task B WHERE B.ID = '"+taskId+"' ) )  AND isdelete = '0'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntityVO.class));
    }

}
